/**
 *	@project:	kybernetes
 *	@author:	D.Toepel
 *	@note:		---
 *	
 *	Copyright (C) 2009  D.Toepel <de.dtools> {at} <gmail> {dot} <com>
 *	
 *	This program is free software: you can redistribute it and/or modify
 *	it under the terms of the GNU General Public License as published by
 *	the Free Software Foundation, either version 3 of the License, or
 *	(at your option) any later version.
 *	
 *	This program is distributed in the hope that it will be useful,
 *	but WITHOUT ANY WARRANTY; without even the implied warranty of
 *	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *	GNU General Public License for more details.
 *	
 *	You should have received a copy of the GNU General Public License
 *	along with this program.  If not, see <http://www.gnu.org/licenses/>.
 *	
 */
package dt.htwk.sql;


import java.io.*;
import java.sql.*;
import java.util.Arrays;

import org.h2.jdbc.JdbcSQLException;
import org.h2.jdbcx.JdbcConnectionPool;

import dt.htwk.extras.WeatherEntry;
import dt.htwk.utilities.User;

public class Database{

	// ***************************************
	// *** DECLARATIONS **********************
	// ***************************************
	
	private JdbcConnectionPool cp;
	private Statement statement;
	private DBResult dbResult;
	private User currUser;
	private WeatherEntry entry;
	
	private String DB_ROOT   = "root";
	private char[] DB_PWORD  = {'g','e','h','e','i','m'};
	private String DB_SCHEMA = "PUBLIC";
	private String DB_URL = "data/" + DB_SCHEMA;
	
	private String fileName = "create.sql";


	// ***************************************
	// *** CONSTRUCTOR ***********************
	// ***************************************
	
	public Database(){
		super();
	}
	
	public Database(String fileName){
		super();
		this.fileName = fileName;
	}
	
	// ***************************************
	// *** PUBLIC METHODS ********************
	// ***************************************
	
	public void setDbData(Object[] args) {
		this.DB_URL = (String) args[0];
		this.DB_SCHEMA = (String) args[1];
		this.DB_ROOT = (String) args[2];
		this.DB_PWORD = (char[]) args[3];
	}

	public void closeConnection(){
		try {
			cp.dispose();
		} catch (SQLException e) {}
	}
	
	public DBResult getDBResult(){return dbResult;}
	
	public DBResult setConnection(){
		dbResult = new DBResult();
		setConnectionPool();
		Connection conn = getConnection();
		
		StringBuilder sb = new StringBuilder();
		for (int i = 0; i < DB_PWORD.length; i++) {
			sb.append(DB_PWORD[i]);
		}
		
		if(!dbResult.isDBExists){
			String url = "jdbc:h2:" + DB_URL;
			cp = JdbcConnectionPool.create(url, DB_ROOT, sb.toString());
			createDatabase();
		}
		
		Arrays.fill(DB_PWORD, '0');
		sb = null;

		selectTable("SELECT * FROM GET_FIRSTLIST;");
		return dbResult;
	}
	
	public DBResult createDatabase(){
		if(cp != null){
			try {
				String[] batch = readTextFile(fileName).split("-- GO");
				Connection conn = getConnection();
				statement = conn.createStatement();
				for (String string : batch) {
					statement.addBatch(string);
				}
				statement.executeBatch();
				conn.close();
				dbResult.isDBExists = true;
				dbResult.isPassword = true;
			} catch (Exception e) {
				dbResult.isDBExists = false;
				dbResult.setSqlComment(e.getLocalizedMessage());
			}
		}
		return dbResult;
	}
	
	public Boolean checkUser(User user){
		selectTable("SELECT pref_localpword FROM N2G_PREFERENCES " +
				"WHERE pref_ID=" + user.getUserID());
		String[][] result = dbResult.getRowContent();
		if(result[0][0].matches(user.getPassword().toString())){
			currUser = user;
			return true;
		}else{
			return false;
		}
	}
	
	public DBResult deleteEntry(Integer id){
		Connection conn = getConnection();
		try {
			statement = conn.createStatement();
			statement.execute("SET SCHEMA " + DB_SCHEMA + ";");
			statement.execute("DELETE FROM ENTRIES WHERE entry_ID=" + id);
			conn.close();
		} catch (SQLException e) {
			// TODO: handle exception
		}
		
		selectTable("SELECT * FROM GET_FIRSTLIST;");
		return dbResult;
	}
	
	/**
	 * <h3>setSimulationData</h3>
	 *
	 * @param entry
	 * @return
	 */
	public DBResult setSimulationData(WeatherEntry entry){
		if(setEntry(entry)){
			if(setSimData(entry)){
				if(setWindData(entry)){
					setGpsData(entry);
					selectTable("SELECT * FROM GET_FIRSTLIST;");
				}
			}
		}
		
		return dbResult;	
	}
	
	public WeatherEntry getSimulationEntry(Integer id){
		entry = new WeatherEntry();
		
		getEntry(id);
		getSimData(id);
		getWindData(id);
		getGpsData(id);
		return entry;
	}
	

	// ***************************************
	// *** PRIVATE METHODS *******************
	// ***************************************
	
	private void setConnectionPool(){
		
		StringBuilder sb = new StringBuilder();
		for (int i = 0; i < DB_PWORD.length; i++) {
			sb.append(DB_PWORD[i]);
		}
		
		String url = "jdbc:h2:" + DB_URL + ";IFEXISTS=TRUE";
		cp = JdbcConnectionPool.create(url, DB_ROOT, sb.toString());
		
//		Arrays.fill(DB_PWORD, '0');
		sb = null;
	}
	
	private Connection getConnection(){
		Connection conn = null;
		try {
			conn = cp.getConnection();
		} catch (JdbcSQLException e) {
			if (e.getErrorCode() == 90013)
				dbResult.isDBExists = false;
			else if (e.getErrorCode() == 8004)
				dbResult.isPassword = false;
			dbResult.setSqlComment(e.getLocalizedMessage());
		} catch (Exception e) {
			dbResult.isDBExists = false;
			dbResult.setSqlComment(e.getLocalizedMessage());
		}
		return conn;
	}
	
	private void selectTable(String sql){
		Connection conn = getConnection();
		try {
			statement = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			statement.execute("SET SCHEMA " + DB_SCHEMA + ";");
			ResultSet result = statement.executeQuery(sql);
			getTableHeader(result);
			getTableContent(result);
			conn.close();
		} catch (SQLException e) {
			dbResult.setSqlComment(e.getLocalizedMessage());
		}
	}
	
	private void selectTable(String sql, Integer userID){
		Connection conn = getConnection();
		try {
			statement = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			statement.execute("SET SCHEMA " + DB_SCHEMA + ";");
			statement.execute("SET @TEMPUID=" + userID + ";");
			ResultSet result = statement.executeQuery(sql);
			getTableHeader(result);
			getTableContent(result);
			conn.close();
		} catch (SQLException e) {
			dbResult.setSqlComment(e.getLocalizedMessage());
		}
	}
	
	private void getTableHeader(ResultSet result){
        String temp = "";
        String tbHeader = "";
		try{
	        ResultSetMetaData resData = result.getMetaData();
	        int columnCount = resData.getColumnCount();
	        for (int i = 1; i <= columnCount; i++) {
	            // ... oder auf den Spaltennamen
	            temp = resData.getColumnName(i);
	            tbHeader += temp + ";";
	        }
		} catch (SQLException e){
			dbResult.setSqlComment(e.getLocalizedMessage());
		}
	}

	
	private void getTableContent(ResultSet result) {
		String temp = "";
		String[][] tbContent = null;
		try {
			ResultSetMetaData resData = result.getMetaData();
			int colCount = resData.getColumnCount();
			int rowCount = 0;
			result.beforeFirst();
			while (result.next()) {
				rowCount++;
			}
			dbResult.setColumnCount(colCount);
			dbResult.setRowCount(rowCount);
			result.beforeFirst();
			tbContent = new String[rowCount][colCount];
			rowCount = 0;
			while (result.next()) {
				for (int i = 1; i <= colCount; i++) {
					temp = result.getString(i);
					if (temp == null) continue;
					tbContent[rowCount][i - 1] = temp;
				}
				rowCount++;
			}
			dbResult.setRowContent(tbContent);
		} catch (Exception e) {
			dbResult.setSqlComment(e.getLocalizedMessage());
		}
	}
	
	private Object[][] getTableContent(String sql, Connection conn) {
		
		ResultSet result = null;
		Object temp = null;
		Object[][] tbContent = null;
		int rowCount, colCount;
		rowCount = colCount = 0;
		
		try {
			statement = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			statement.execute("SET SCHEMA " + DB_SCHEMA + ";");
			result = statement.executeQuery(sql);
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		try {
			ResultSetMetaData resData = result.getMetaData();
			colCount = resData.getColumnCount();
			result.beforeFirst();
			while (result.next()) {
				rowCount++;
			}
			result.beforeFirst();
			tbContent = new Object[rowCount][colCount];
			rowCount = 0;
			while (result.next()) {
				for (int i = 1; i <= colCount; i++) {
					temp = result.getObject(i);
					if (temp == null) continue;
					tbContent[rowCount][i - 1] = temp;
				}
				rowCount++;
			}
			return tbContent;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	
    private String readTextFile(String filename){
    	String line;
    	String sqlQuery = "";
		try
		{
			InputStream fis = getClass().getResourceAsStream(filename);
			BufferedReader reader=new BufferedReader ( new InputStreamReader(fis));
			while((line = reader.readLine()) != null)
			{
				sqlQuery += line +"\n";
			}
			reader.close();
		}
		catch (Exception e) {}
		return sqlQuery;
    }
    
    
/* *********************************************************************
 * WInd und GPS Simulation
 */
    
    /**
     * <h3>setEntry</h3>
     *
     * @param entry
     * @return
     */
    private Boolean setEntry(WeatherEntry entry){
		String sql;
		Integer entryID,i;
		entryID = i = 0;
		Connection conn = getConnection();
		Timestamp timeStamp = new java.sql.Timestamp(new java.util.Date().getTime());

		if(entry.getEntryID() == null){
			sql = "INSERT INTO ENTRIES (" +
					"entry_Time,entry_Descreption)" +
					"VALUES(?,?);";
		}else{
			entryID = entry.getEntryID();
			sql = "UPDATE ENTRIES SET " +
			"entry_Time=?,entry_Descreption=? " +
			"WHERE entry_ID=" + entryID + ";";
		}

        try {
        	statement = conn.createStatement();
			statement.execute("SET SCHEMA WEATHER;");
			
        	PreparedStatement prep = conn.prepareStatement(sql);
			prep.setTimestamp(++i, timeStamp);
			prep.setString(++i, entry.getDescreption());
			prep.execute();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

    /**
     * <h3>setSimData</h3>
     *
     * @param entry
     * @return
     */
    private Boolean setSimData(WeatherEntry entry){
		String sql;
		Integer entryID,i;
		entryID = i = 0;
		Connection conn = getConnection();
		
		if(entry.getEntryID() == null){
			sql = "INSERT INTO SETTINGS (" +
					"entry_ID,set_SingleMode,set_StepTime," +
					"set_ServerAddress,set_ServerPort,set_SendOnceGPS)" +
					"VALUES((SELECT MAX(entry_ID) FROM ENTRIES),?,?,?,?,?);";
		}else{
			entryID = entry.getEntryID();
			sql = "UPDATE SETTINGS SET " +
			"set_SingleMode=?,set_StepTime=?," +
			"set_ServerAddress=?,set_ServerPort=?,set_SendOnceGPS=? " +
			"WHERE entry_ID=" + entryID + ";";
		}

        try {
        	statement = conn.createStatement();
			statement.execute("SET SCHEMA WEATHER;");
			
        	PreparedStatement prep = conn.prepareStatement(sql);
			prep.setBoolean(++i, entry.getSingleMode());
			prep.setDouble(++i, entry.getStepTime());
			prep.setString(++i, entry.getServerAddress());
			prep.setInt(++i, entry.getServerPort());
			prep.setBoolean(++i, entry.getSendGpsOnce());
			prep.execute();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
    
    private Boolean setWindData(WeatherEntry entry){
		String sql,idString;
		Integer entryID,i;
		entryID = i = 0;
		Connection conn = getConnection();
		String[][] windData = entry.getWindData();
		
		if(entry.getEntryID() == null){
			idString = "(SELECT MAX(entry_ID) FROM ENTRIES)";
		}else{
			entryID = entry.getEntryID();
			idString = entry.getEntryID().toString();
		}
		
		sql = "INSERT INTO WINDDATA (" +
				"entry_ID,wind_strength," +
				"wind_direction,wind_fluctuation) VALUES";
		for (int j = 0; j < entry.getWindData().length; j++) {
			if (j > 0) sql += ",";
			sql += " (" + idString + ",?,?,?)";
		}
		
		sql += ";";
		
        try {
        	statement = conn.createStatement();
			statement.execute("SET SCHEMA WEATHER;");
			
			if(entry.getEntryID() != null){
				statement.execute("DELETE FROM WINDDATA WHERE entry_ID=" + entryID + ";");
			}
			
        	PreparedStatement prep = conn.prepareStatement(sql);
        	for (int j = 0; j < entry.getWindData().length; j++) {
	        	prep.setString(++i, windData[j][0]);
				prep.setString(++i, windData[j][1]);
				prep.setString(++i, windData[j][2]);
        	}
			prep.execute();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
    
    private Boolean setGpsData(WeatherEntry entry){
		String sql;
		Integer entryID,i;
		entryID = i = 0;
		Connection conn = getConnection();
		Double[][] gpsData = entry.getGpsData();
		
		if(entry.getEntryID() == null){
			sql = "INSERT INTO GPSDATA (" +
					"entry_ID,gps_BaseLon,gps_BaseLat," +
					"gps_LuvLon,gps_LuvLat," +
					"gps_LeeLeftLon,gps_LeeLeftLat," +
					"gps_LeeRightLon,gps_LeeRightLat," +
					"gps_StartLeftLon,gps_StartLeftLat," +
					"gps_StartRightLon,gps_StartRightLat," +
					"gps_BoxLeftTopLon,gps_BoxLeftTopLat," +
					"gps_BoxLeftBottomLon,gps_BoxLeftBottomLat," +
					"gps_BoxRightTopLon,gps_BoxRightTopLat," +
					"gps_BoxRightBottomLon,gps_BoxRightBottomLat)" +
					"VALUES((SELECT MAX(entry_ID) FROM ENTRIES),?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
		}else{
			entryID = entry.getEntryID();
			sql = "UPDATE GPSDATA SET " +
			"gps_BaseLon=?,gps_BaseLat=?," +
			"gps_LuvLon=?,gps_LuvLat=?," +
			"gps_LeeLeftLon=?,gps_LeeLeftLat=?," +
			"gps_LeeRightLon=?,gps_LeeRightLat=?," +
			"gps_StartLeftLon=?,gps_StartLeftLat=?," +
			"gps_StartRightLon=?,gps_StartRightLat=?," +
			"gps_BoxLeftTopLon=?,gps_BoxLeftTopLat=?," +
			"gps_BoxLeftBottomLon=?,gps_BoxLeftBottomLat=?," +
			"gps_BoxRightTopLon=?,gps_BoxRightTopLat=?," +
			"gps_BoxRightBottomLon=?,gps_BoxRightBottomLat=? " +
			"WHERE entry_ID=" + entryID + ";";
		}

        try {
        	statement = conn.createStatement();
			statement.execute("SET SCHEMA WEATHER;");
			
        	PreparedStatement prep = conn.prepareStatement(sql);
        	for (int j = 0; j < gpsData.length; j++) {
        		prep.setDouble(++i, gpsData[j][0]);
    			prep.setDouble(++i, gpsData[j][1]);
			}
			prep.execute();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
    
	
	/**
	 * <h3>getEntry</h3>
	 *
	 * @param id
	 */
	private void getEntry(Integer id) {
		String sql = "SELECT * FROM ENTRIES WHERE entry_ID=" + id + ";";
		Connection conn = getConnection();
		Object[][] result = getTableContent(sql, conn);
		
		entry.setEntryID((Integer)result[0][0]);
		entry.setTimestamp((Timestamp)result[0][1]);
		entry.setDescreption((String)result[0][2]);		
		
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * <h3>getSimData</h3>
	 *
	 * @param id
	 */
	private void getSimData(Integer id) {
		String sql = "SELECT * FROM SETTINGS WHERE entry_ID=" + id + ";";
		Connection conn = getConnection();
		Object[][] result = getTableContent(sql, conn);
		
		entry.setSingleMode((Boolean)result[0][2]);
		entry.setStepTime((Double)result[0][3]);
		entry.setServerAddress((String)result[0][4]);
		entry.setServerPort((Integer)result[0][5]);
		entry.setSendGpsOnce((Boolean)result[0][6]);
		
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * <h3>getWindData</h3>
	 *
	 * @param id
	 */
	private void getWindData(Integer id) {
		String sql = "SELECT * FROM WINDDATA WHERE entry_ID=" + id + ";";
		Connection conn = getConnection();
		Object[][] result = getTableContent(sql, conn);
		String[][] windData = new String[result.length][3];
		
		for (int i = 0; i < result.length; i++) {
			windData[i][0] = (String)result[i][2];
			windData[i][1] = (String)result[i][3];
			windData[i][2] = (String)result[i][4];
		}
		entry.setWindData(windData);
		
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * <h3>getGpsData</h3>
	 *
	 * @param id
	 */
	private void getGpsData(Integer id) {
		String sql = "SELECT * FROM GPSDATA WHERE entry_ID=" + id + ";";
		Connection conn = getConnection();
		Object[][] result = getTableContent(sql, conn);
		Double[][] gpsData = new Double[10][2];
		
		for (int i = 0; i < 10; i++) {
			gpsData[i][0] = (Double)result[0][2*i+2];
			gpsData[i][1] = (Double)result[0][2*i+3];
		}
		entry.setGpsData(gpsData);
		
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

    

}
